﻿using System;
using System.Collections.Generic;
using System.Text;

/*
 * 函数名统一小写
 */

namespace DBUtil.SqlSegment
{
    /// <summary>
    /// 数据库字符串运算符，示例：
    /// <list type="number">
    /// <item>sqlserver: GetLength("name",true) => "len(isnull(name,''))"</item>
    /// <item>mysql: GetLength("name",true) => "length(ifnull(name,''))"</item>
    /// </list>
    /// </summary>
    public abstract class StringSqlSegment
    {
        private readonly DBAccess db;

        public StringSqlSegment(DBAccess db)
        {
            this.db = db;
        }

        /// <summary>
        /// 获取字符串的长度，示例：
        /// <list type="number">
        /// <item>sqlserver: GetLength("name",true) => "len(isnull(name,''))"</item>
        /// <item>mysql: GetLength("name",true) => "length(ifnull(name,''))"</item>
        /// </list>
        /// </summary>
        /// <param name="input">输入字符串,关键字(如:列名)直接传入, 普通字符串请先调用: input.WrapToSql()</param>
        /// <param name="null2Zero">null当做长度为0</param>
        /// <returns></returns>
        public abstract string GetLength(string input, bool null2Zero = false);

        /// <summary>
        /// 去除列的字符串左右空格，示例：
        /// <list type="number">
        /// <item>sqlserver(&lt;2017): Trim("name") => "ltrim(rtrim(name))"</item>
        /// <item>sqlserver(>=2017): Trim("name") => "trim(name)"</item>
        /// <item>mysql: Trim("name") => "trim(name)"</item>
        /// </list>
        /// </summary>
        /// <param name="input">输入字符串,关键字(如:列名)直接传入, 普通字符串请先调用: input.WrapToSql()</param>
        /// <returns></returns>
        public abstract string Trim(string input);

        /// <summary>
        /// 去除列的字符串左空格，示例：
        /// <list type="number">
        /// <item>sqlserver: LeftTrim("name") => "ltrim(name)"</item>
        /// <item>mysql: LeftTrim("name") => "ltrim(name)"</item>
        /// </list>
        /// </summary>
        /// <param name="input">输入</param>
        /// <returns></returns>
        public abstract string LeftTrim(string input);

        /// <summary>
        /// 去除列的字符串右空格，示例：
        /// <list type="number">
        /// <item>sqlserver: RightTrim("name") => "rtrim(name)"</item>
        /// <item>mysql: RightTrim("name") => "rtrim(name)"</item>
        /// </list>
        /// </summary>
        /// <param name="input">输入</param>
        /// <returns></returns>
        public abstract string RightTrim(string input);

        /// <summary>
        /// 转为大写，示例：
        /// <list type="number">
        /// <item>sqlserver: Upper("name") => "upper(name)"</item>
        /// <item>mysql: Upper("name") => "upper(name)"</item>
        /// </list>
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public abstract string Upper(string input);

        /// <summary>
        /// 转为小写，示例：
        /// <list type="number">
        /// <item>sqlserver: Lower("name") => "lower(name)"</item>
        /// <item>mysql: Lower("name") => "lower(name)"</item>
        /// </list>
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public abstract string Lower(string input);

        /// <summary>
        /// 字符串替换，示例：
        /// <list type="number">
        /// <item>sqlserver: ReplaceAll("name","+".WrapToSql(),"-".WrapToSql()) => "replace(name,'+','-')"</item>
        /// <item>mysql: ReplaceAll("name","+".WrapToSql(),"-".WrapToSql()) => "replace(name,'+','-')"</item>
        /// </list>
        /// </summary>
        /// <param name="input"></param>
        /// <param name="oldStr"></param>
        /// <param name="newStr"></param>
        /// <returns></returns>
        public abstract string ReplaceAll(string input, string oldStr, string newStr);

        /* mysql SUBSTRING_INDEX
SELECT 
	SUBSTRING_INDEX('www.mysql.com', '.', 1) as c1, -- www
	SUBSTRING_INDEX('www.mysql.com', '.', 2) as c2, -- www.mysql
	SUBSTRING_INDEX('www.mysql.com', '.', 3) as c3, -- www.mysql.com
	SUBSTRING_INDEX('www.mysql.com', '.', 4) as c4, -- www.mysql.com
	
	SUBSTRING_INDEX('www.mysql.com', '.', -1) as c_1, -- com
	SUBSTRING_INDEX('www.mysql.com', '.', -2) as c_2, -- mysql.com
	SUBSTRING_INDEX('www.mysql.com', '.', -3) as c_3, -- www.mysql.com
	SUBSTRING_INDEX('www.mysql.com', '.', -4) as c_4  -- www.mysql.com

SELECT 
	SUBSTRING_INDEX('www^_^mysql^_^com', '^_^', 1) as c1, -- www
	SUBSTRING_INDEX('www^_^mysql^_^com', '^_^', 2) as c2, -- www^_^mysql
	SUBSTRING_INDEX('www^_^mysql^_^com', '^_^', 3) as c3, -- www^_^mysql^_^com
	SUBSTRING_INDEX('www^_^mysql^_^com', '^_^', 4) as c4, -- www^_^mysql^_^com
	
	SUBSTRING_INDEX('www^_^mysql^_^com', '^_^', -1) as c_1, -- com
	SUBSTRING_INDEX('www^_^mysql^_^com', '^_^', -2) as c_2, -- mysql^_^com
	SUBSTRING_INDEX('www^_^mysql^_^com', '^_^', -3) as c_3, -- www^_^mysql^_^com
	SUBSTRING_INDEX('www^_^mysql^_^com', '^_^', -4) as c_4  -- www^_^mysql^_^com
         */

        /* sqlserver reverse/substring/charindex
select 
	substring('www.myslq.com',0,charindex('.','www.myslq.com',0)) as c1, -- www
	reverse(substring(reverse('www.mysql.com'),0,charindex('.',reverse('www.mysql.com'),0))) as c_1 -- com

select	
	substring('www^_^myslq^_^com',0,CHARINDEX('^_^','www^_^myslq^_^com',0)) as c1,-- www
	reverse(substring(reverse('www^_^mysql^_^com'),0,charindex('^_^',reverse('www^_^mysql^_^com'),0))) as c_1 -- com
         */

        /// <summary>
        /// 按照指定字符串分割,取最开始的那个,示例:
        /// <list type="number">
        /// <item>sqlserver: SplitFirst("name","-".WrapToSql()) => "substring(name,0,charindex('-',name,0))"</item>
        /// <item>mysql: SplitFirst("name","-".WrapToSql()) => "substring_index(name, '-', 1)"</item>
        /// </list>
        /// </summary>
        /// <param name="input"></param>
        /// <param name="pattern"></param>
        /// <returns></returns>
        public abstract string SplitFirst(string input, string pattern);

        /// <summary>
        /// 返按照指定字符串分割,取最后的那个,示例:
        /// <list type="number">
        /// <item>sqlserver: SplitLast("name","-".WrapToSql()) => "reverse(substring(reverse(name),0,charindex('-',reverse(name),0)))"</item>
        /// <item>mysql: SplitLast("name","-".WrapToSql()) => "substring_index(name, '-', -1)"</item>
        /// </list>
        /// </summary>
        /// <param name="input"></param>
        /// <param name="pattern"></param>
        /// <returns></returns>
        public abstract string SplitLast(string input, string pattern);

        /// <summary>
        /// 字符串相加,示例:
        /// <list type="number">
        /// <item>sqlserver: Add("name","age") => "name+age"</item>
        /// <item>mysql: Add("name","age") => "concat(name,age)"</item>
        /// </list>
        /// </summary>
        /// <param name="input"></param>
        /// <param name="input2"></param>
        /// <returns></returns>
        public abstract string Add(string input, string input2);
    }
}
